import os
import re
import sys

import pymysql
import concurrent.futures

# 将项目根目录添加到 Python 解释器的搜索路径中
current_dir = os.path.dirname(os.path.abspath(__file__))
project_root = os.path.abspath(os.path.join(current_dir, ".."))
sys.path.append(project_root)

from openapi import follows_api




# 判断是否存在特殊字符
def has_special_characters(text):
    pattern = r'[^\w\s]'
    match = re.search(pattern, text)
    return match is not None

# 查询前100条数据
def fetch_data():
    conn = pymysql.connect(
        host="43.156.247.50",  # 替换为实际的主机名
        user="root",  # 替换为实际的用户名
        password="520777",  # 替换为实际的密码
        database="house"  # 替换为实际的数据库名
    )
    sql = "select house_id from house where create_time >= '2024-04-01 09:02:36' and have_logs != 0 order by create_time desc"
    cursor = conn.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 更新数据
def update_data(row):
    try:
        house_id = row[0]

        # 开始查询当前的房产日志信息，并入库
        flowers = follows_api.getFollows(house_id)

        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        cursor = conn.cursor()
            
        if len(flowers) > 0:
            for flower in flowers:
                try:
                    escaped_str = flower['content']
                    # 转义特殊字符
                    if has_special_characters(escaped_str):
                        escaped_str = escaped_str.encode('unicode-escape').decode()
                        
                    # 构建插入数据的 SQL 查询
                    sql = "INSERT INTO house.house_follow(follow_id, house_id, log_type, stars, user_name, content, create_time, create_user_id) VALUES(%s, %s, %s, %s, %s, %s, %s, %s);"
                    values = (
                        flower['id'],
                        house_id,
                        flower['logType'],
                        flower['stars'],
                        flower['userName'],
                        escaped_str,
                        flower['createTimeString'],
                        flower['createUserId']
                    )

                    # 执行 SQL 查询
                    cursor.execute(sql, values)
                    conn.commit()
                except Exception as e:
                    print("发生异常:", str(e))
        else:
            try:
                sql = "update house set have_logs = 0 where house_id = %s;"
                values = (house_id)
                # 执行 SQL 查询
                cursor.execute(sql, values)
                conn.commit()
            except Exception as e:
                print("发生异常:", str(e))
                
        cursor.close()
        conn.close()
            

        print(f"更新{house_id}成功{len(flowers)}")
    except Exception as e:
        print(f"未知异常:", str(e))

# 主函数
def main():
    data = fetch_data()
    while len(data) > 0:
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            futures = [executor.submit(update_data, row) for row in data]
            concurrent.futures.wait(futures)
            
        data = fetch_data()


if __name__ == '__main__':
    main()